package com.pagepub.expo.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;

import com.pagepub.expo.entity.CorpProducts;
import com.pagepub.expo.entity.IdName;

@Repository
public class CorpProductsDao {
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate template;
	
	@Autowired
	@Qualifier("appSeqDao")
	private AppSeqDao appSeqDao;
	
	private static String tableName="Corp_Products";
	
	private static String ID_NAME="SELECT id, productName name,catalogDesc FROM "+tableName;
	
	private static String QUERY_ALL = "SELECT id,productName,content,clickNum,sequence," +
			"isCommFlag,isNewFlag,addTime,updateTime,corpId," +
			"sortId,productNo,productModel,nPrice,pPrice," +
			"stock,unitName,smallPic,bigPic,marker," +
			"keyword,productType,isAuditing  FROM "+tableName;

	private static String QUERY_ALL_COUNT = "SELECT COUNT(*) FROM "+  tableName;
	
	private static String INSERT_SQL = "INSERT  INTO "+tableName+" (" +
			"id,productName,content,clickNum,sequence," +
			"isCommFlag,isNewFlag,addTime,updateTime,corpId," +
			"sortId,productNo,productModel,nPrice,pPrice," +
			"stock,unitName,smallPic,bigPic,marker," +
			"keyword,productType,isAuditing)VALUES(" +
			":id,:productName,:content,:clickNum,:sequence," +
			":isCommFlag,:isNewFlag,:addTime,:updateTime,:corpId," +
			":sortId,:productNo,:productModel,:nPrice,:pPrice," +
			":stock,:unitName,:smallPic,:bigPic,:marker," +
			":keyword,:productType,:isAuditing)";

	private static String DELETE_SQL = "DELETE FROM "+ tableName +" WHERE id=?";

	private static String UPDATE_SQL = "UPDATE "+ tableName + " SET " +
			"productName=:productName,content=:content,clickNum=:clickNum,sequence=:sequence," +
			"isCommFlag=:isCommFlag,isNewFlag=:isNewFlag,updateTime=:updateTime,corpId=:corpId," +
			"sortId=:sortId,productNo=:productNo,productModel=:productModel,nPrice=:nPrice,pPrice=:pPrice," +
			"stock=:stock,unitName=:unitName,smallPic=:smallPic,bigPic=:bigPic,marker=:marker," +
			"keyword=:keyword,productType=:productType, isAuditing=:isAuditing "+
			"WHERE id=:id";

	private final ParameterizedRowMapper<CorpProducts> mapper = new ParameterizedRowMapper<CorpProducts>() {
		public CorpProducts mapRow(ResultSet rs, int rowNum) throws SQLException {
			CorpProducts obj = new CorpProducts();
			obj.setId(rs.getLong("id"));
			obj.setProductName(rs.getString("productName"));
			obj.setContent(rs.getString("content"));
			obj.setClickNum(rs.getInt("clickNum"));
			obj.setSequence(rs.getInt("sequence"));
			obj.setIsCommFlag(rs.getShort("isCommFlag"));
			obj.setIsNewFlag(rs.getShort("isNewFlag"));
			obj.setAddTime(rs.getTimestamp("addTime"));
			obj.setUpdateTime(rs.getTimestamp("updateTime"));
			obj.setCorpId(rs.getString("corpId"));
			obj.setSortId(rs.getString("sortId"));
			obj.setProductNo(rs.getString("productNo"));
			obj.setProductModel(rs.getString("productModel"));
			obj.setnPrice(rs.getInt("nPrice"));
			obj.setpPrice(rs.getInt("pPrice"));
			obj.setStock(rs.getInt("stock"));
			obj.setUnitName(rs.getString("unitName"));
			obj.setSmallPic(rs.getString("smallPic"));
			obj.setBigPic(rs.getString("bigPic"));
			obj.setMarker(rs.getString("marker"));
			obj.setKeyword(rs.getString("keyword"));
			obj.setProductType(rs.getString("productType"));
			obj.setIsAuditing(rs.getInt("isAuditing"));
			return obj;
		}
	};

	public List<CorpProducts> findAll() {
		return template.query(QUERY_ALL, mapper);
	}

	public CorpProducts findById(Long id) {
		List<CorpProducts> list = template.query(QUERY_ALL + " WHERE id=?",
				new Object[] { id }, mapper);
		if (list.size() == 0) {
			return null;
		} else
			return list.get(0);
	}
	
	

	
	public void insert(CorpProducts bean) {
		if(bean.getId()==null){
			bean.setId(new Long(appSeqDao.getSequence("corp_products_seq")));
		}		
		SqlParameterSource ps = new BeanPropertySqlParameterSource(bean);
		NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
				template.getDataSource());
		npTemplate.update(INSERT_SQL, ps);

	}

	public void delete(Long id) {
		template.update(DELETE_SQL, id);
	}

	public void update(CorpProducts bean) {
		SqlParameterSource ps = new BeanPropertySqlParameterSource(bean);
		NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
				template.getDataSource());
		npTemplate.update(UPDATE_SQL, ps);
	}
	
	public int getCount(Map<String, String> param){
		int count = template.queryForInt(QUERY_ALL_COUNT);
		return count;
	}
	
	public List findByPage(int currentPage,int pageSize,Map<String, String> param){
		List lists = template.query(QUERY_ALL + " limit ?,?",
				new Object[] { currentPage, pageSize}, mapper);
		return lists;
		
	}
	
	
	public List findByPage(int currentPage,int pageSize,Long corpId){
		List lists = template.query(QUERY_ALL+" where corpId=?" + " limit ?,?",
				new Object[] {corpId, currentPage, pageSize}, mapper);
		return lists;
		
	}
	
	
	public List<IdName> findIdNames(){
		List lists = template.query(ID_NAME, IdName.idNameMapper);
		return lists;
	}
	
	public void audit(Long id, boolean pass){
		int aduiting=0;
		if(pass==true){
			aduiting=1;
		}
		template.update("update " + tableName+" set isAuditing=? where id=?", new Object[]{aduiting,id});
	}
}
